package com.mengtian_tech.tju.dataquery.api.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.github.pagehelper.util.StringUtil;

@Service
public class QueryPersonService {
	
	@Autowired
	@Qualifier("jdbcTemplateThimis")
	JdbcTemplate jdbcTemplateThimis;
	
	
	@Autowired
	@Qualifier("jdbcTemplateDataAi")
	JdbcTemplate jdbcTemplateDataAi;
	
	@Autowired
	SchoolLeaderService schoolLeaderService;
	

	
	public List<Map<String,Object>> queryUser(String key){
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		
		//查询员工
		List<Map<String,Object>> list = this.jdbcTemplateThimis.queryForList(
				"select * from(select  t1.college, t.staff_id,t.name,depart,apost  from app_manager.employee t ,app_manager.dict_college t1"
				+ "  where ( t.name like ?  or t.staff_id like ? ) and t.college_id = t1.college_id order by t.apostlevel_id asc,t.college_id asc ) where rownum < 100",
				"%"+key+"%","%"+key+"%");
		
		result.addAll(list);
		
		return result;
	}
	
	
	public List<Map<String,Object>> queryUser(int pageNo,String key,String tpostlevel,String diploma_id,String rpolity){
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		
		int pageSize = 20;
		
		String sql = "select  t1.college,t.CELL_PHONE, t.staff_id,t.name,depart,apost,t.apostlevel_id,t.status_id, t.rpolity_id,t.diploma_id , "
				+ " decode(t.status_id,'01','021',t.status_id) status1 ,   decode(t.country_id,'156',1,2) is_foreign  "
				+ " from app_manager.employee t ,app_manager.dict_college t1"
				+ "  where ( t.name like ?  or t.staff_id like ? ) and t.college_id = t1.college_id  and t.staff_id not like 'w%' ";
				
		List<Object> params  = new ArrayList<Object>(); 
		params.add("%"+key+"%");
		params.add("%"+key+"%");
		
		if(!"0".equals(tpostlevel)) {
			
			if("1".equals(tpostlevel)) {
				sql += " and t.apostlevel_id in (?,?)";
				params.add("41");
				params.add("43");
			}
			
			if("2".equals(tpostlevel)) {
				sql += " and t.apostlevel_id in (?,?)";
				params.add("42");
				params.add("44");
			}			
			

			if("3".equals(tpostlevel)) { //校领导
				List<String> leaders = schoolLeaderService.getSchoolLeaderIds();
				StringBuilder leaderStr = new StringBuilder();
				for(int i=0;i<leaders.size();i++) {
					String leader = leaders.get(i);
					leaderStr.append("'");
					leaderStr.append(leader);
					leaderStr.append("'");
					
					if(i != leaders.size() -1) {
						leaderStr.append(",");
					}
				}
				
				sql += " and t.staff_id in ( "+ leaderStr.toString() +"  )";
				
			}			
		}
		
		if(!"0".equals(diploma_id)) {
			sql += " and t.diploma_id = ? ";
			params.add(diploma_id);		
		}
		
		if(!"0".equals(rpolity)) {
			sql += " and t.rpolity_id = ? ";
			params.add(rpolity);		
		}
		
		//sql += " and  t.status_id not  in ('21','22','31','32','33','34','35','36','37','39') ";
		
		//先人员类型排序，教职工考前，然后是状态,然后是 姓氏笔画，最后是工号
		sql += "   order by t.stafftype_id  asc  , status1 asc ,    is_foreign asc,    nlssort(t.name,'NLS_SORT=SCHINESE_STROKE_M') , t.staff_id asc   ";
		
		//加上分页
		String resultSql = "SELECT * FROM (SELECT a.*, ROWNUM rnum FROM ("+sql + ") a WHERE ROWNUM <= "+ (pageNo*pageSize)+") WHERE rnum > "+(pageNo-1)*pageSize;
		
		System.out.println(resultSql);
		System.err.println(params);
		
		//查询员工
		List<Map<String,Object>> list = this.jdbcTemplateThimis.queryForList(resultSql, params.toArray());
		
		result.addAll(list);
		
		return result;
	}
	
	public List<Map<String,Object>> queryUserByName(String key){
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		
		//查询员工
		List<Map<String,Object>> list = this.jdbcTemplateThimis.queryForList(
				"select * from(select  t1.college, t.staff_id,t.name,depart,apost  from app_manager.employee t ,app_manager.dict_college t1"
				+ "  where ( t.name like ?   ) and t.college_id = t1.college_id  and "
				+ " (staff_id not like 'w%' and staff_id not like 'W%' ) ) where rownum < 100",
				"%"+key+"%");
		
		result.addAll(list);
		
		return result;
	}
	
	
	/**
	 * 查询进出记录
	 */
	public List<Map<String,Object>> queryVisitLog(String staffId,Integer pageNo) {
		if(pageNo == null) pageNo  = 1;
		int pageSize = 30;
		return this.jdbcTemplateDataAi.queryForList(
				"select job_no,name, send_time created_at,src_name,school_area,direction,college_name from user_gate_log where job_no = ? order by id desc limit ?,?",
				staffId,(pageNo-1)*pageSize,pageSize);
	}


	public Map<String, Object> queryOneUser(String staffId) {
		//查询员工
		return this.jdbcTemplateThimis.queryForMap(
				"select  t1.college,t.CELL_PHONE, t.staff_id,t.name,depart,apost,apostlevel_id,status_id, t.apostlevel_id  from app_manager.employee t ,app_manager.dict_college t1"
				+ "  where  t.staff_id = ?  and t.college_id = t1.college_id ",
				staffId);
		
	}
	
	public Date getAsyncDate() {
		Map<String,Object>  map = this.jdbcTemplateDataAi.queryForMap("select task_begin from async_log_person_gate order by id desc limit 0,1");
		return (Date) map.get("task_begin");
	}
	
	/**
	 * 查询进出记录
	 */
	public List<Map<String,Object>> queryAllVisitList(Integer pageNo,String school,String gate,String begin,String end) {
		if(pageNo == null) pageNo  = 1;
		int pageSize = 30;
		
		String sql = "select job_no,name,org_name, send_time created_at,src_name,school_area,direction,college_name from user_gate_log where 1=1 ";
		
		List<Object> params =  new ArrayList<Object>();
		
		if(StringUtil.isNotEmpty(school)) {
			sql += "and school_area = ? ";
			params.add(school);
		}
		
		if(StringUtil.isNotEmpty(gate)) {
			sql += "and gate_name = ? ";
			params.add(gate);
		}
		
		if(StringUtil.isNotEmpty(begin)) {
			sql += "and date_on >= ? ";
			params.add(begin);
		}
		
		if(StringUtil.isNotEmpty(end)) {
			sql += "and date_on <= ? ";
			params.add(end);
		}
		
		
		sql += " order by id desc limit ?,?";
		params.add((pageNo-1)*pageSize);
		params.add(pageSize);
		
		
		return this.jdbcTemplateDataAi.queryForList(sql,params.toArray());
	}
}
